Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Imports System.Data.DataSet
Imports System.Data.Sql

Module ModuloPrincipal
    'Declaracion de Objetos ADO .NET
    'Crear Objeto Conexion SQL Server
    Friend SQLConn As New SqlConnection

    'Crear Objeto Comando SQL
    Friend SQLCmd As SqlCommand

    'Crear Objeto DataReader
    Friend drDataReader As SqlDataReader

    'Variable que almacena cadena de conexion a la BD.
    Public vsConnString As String = ""

    'Variables de Usuario y Password
    Friend vsUser, vsPassword As String

    'Variable para almacenar instrucciones SQL
    Public strSQL As String

    'Dataset  
    Public ds As DataSet
    'Adaptadores para cargar Grid
    Friend adapter As SqlDataAdapter
    'Procedimiento generico para Cargar un ComboBox
    Public Sub CargarDataSet(ByVal vsNombreTabla As String, ByRef vDataSet As DataSet, ByRef Combo As ComboBox)
        Try
            'Me.Cursor = Cursors.WaitCursor
            'Abrir conexion a la BD
            SQLConn.Open()

            'Preparar instruccion SQL
            strSQL = "select * from " + vsNombreTabla
            'Vaciar DataSet
            vDataSet.Clear()

            'Declarar DataAdapter para cargar combo
            Dim daTipoEquipo As New SqlDataAdapter(strSQL, SQLConn)

            daTipoEquipo.Fill(vDataSet, vsNombreTabla)

            Combo.DataSource = vDataSet.Tables(vsNombreTabla)

            Combo.Text = ""

            daTipoEquipo = Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            'Me.Cursor = Cursors.Default
            SQLConn.Close()
        End Try
    End Sub

    Public Sub CargarDataSet(ByVal vsNombreTabla As String, ByRef vDataSet As DataSet, ByRef Grilla As DataGridView, ByVal Parametro As String)
        Try
            'Abrir conexion a la BD
            SQLConn.Open()

            strSQL = "Select * From " & vsNombreTabla & " Where Modelo like '%" & Parametro & "%'"
            'Vaciar DataSet
            vDataSet.Clear()

            'Declarar DataAdapter para cargar grilla
            Dim daTipoEquipo As New SqlDataAdapter(strSQL, SQLConn)
            daTipoEquipo.SelectCommand.CommandType = CommandType.Text

            daTipoEquipo.Fill(vDataSet, vsNombreTabla)

            Grilla.DataSource = vDataSet.Tables(vsNombreTabla)

            daTipoEquipo = Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            'Me.Cursor = Cursors.Default
            SQLConn.Close()
        End Try
    End Sub

    'Funcion que Devuelve un DataTable. Recibe como parametro el nombre de una tabla
    Public Function CargarDataTable(ByVal vsNombreTabla As String) As DataTable
        Dim vDataTable As New DataTable

        Try
            'Abrir conexion a la BD
            SQLConn.Open()

            'Preparar instruccion SQL
            strSQL = "select * from " + vsNombreTabla

            'Declarar DataAdapter para cargar DataTable
            Dim daAdaptador As New SqlDataAdapter(strSQL, SQLConn)

            'Asignar Nombre al DataTable
            vDataTable.TableName = vsNombreTabla

            'Ejecutar FILL del Adaptador de Datos.
            daAdaptador.Fill(vDataTable)

            daAdaptador.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            'DataTable Nulo.
            vDataTable = Nothing
        Finally
            SQLConn.Close()
        End Try
        'Devolver DataSet
        Return vDataTable

    End Function


    'Funcion que Devuelve un DataTable. Recibe como parametro la instruccion SQL
    Public Function EjecutarSelect(ByVal strSQL As String) As DataTable
        Dim vDataTable As New DataTable

        Try
            'Abrir conexion a la BD
            SQLConn.Open()

            'Declarar DataAdapter para cargar DataTable
            Dim daAdaptador As New SqlDataAdapter(strSQL, SQLConn)

            'Ejecutar FILL del Adaptador de Datos.
            daAdaptador.Fill(vDataTable)

            daAdaptador.Dispose()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            'DataTable Nulo.
            vDataTable = Nothing
        Finally
            SQLConn.Close()
        End Try
        'Devolver DataSet
        Return vDataTable

    End Function

    Public Sub EjecutarComandoSQL(ByVal strSQL As String)
        Try
            'Abrir Conexion ala BD
            SQLConn.Open()
            'Crear Comando SQL
            SQLCmd = New SqlCommand(strSQL, SQLConn) 'Crear comando, a partir del parametro recibido
            'Ejecutar Comando SQL
            SQLCmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            SQLConn.Close()
        End Try

    End Sub

    Public Function EjecutarComandoSQL2(ByVal strSQL As String) As Double
        Dim total As Double
        Try
            'Abrir Conexion ala BD
            SQLConn.Open()
            'Crear Comando SQL
            SQLCmd = New SqlCommand(strSQL, SQLConn) 'Crear comando, a partir del parametro recibido
            'Ejecutar Comando SQL
            total = SQLCmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            SQLConn.Close()
        End Try
        Return total
    End Function

    Public Sub EjecutarComandoSQL(ByVal strSQL As String, ByVal ParamArray vParametros() As Object)
        Try
            If Not SQLConn.State = ConnectionState.Open Then

            End If
            'Abrir Conexion ala BD
            SQLConn.Open()
            'Crear Comando SQL
            SQLCmd = New SqlCommand(strSQL, SQLConn) 'Crear comando, a partir del parametro recibido

            'Asignar Parametros al objeto Comando. Recorrer 
            Dim i As Integer = 0
            For Each vParametro As Object In vParametros
                i += 1
                Dim SQLParam As New SqlClient.SqlParameter()
                SQLCmd.Parameters.Add(New SqlClient.SqlParameter("@" & i.ToString(), vParametro))
            Next

            'Ejecutar Comando SQL
            SQLCmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            SQLConn.Close()
        End Try

    End Sub

    Public Function CargarDataSet(ByVal vsNombreTabla As String) As DataSet
        Dim vDataSet As New DataSet

        Try

            'Me.Cursor = Cursors.WaitCursor
            'Abrir conexion a la BD
            SQLConn.Open()

            'Preparar instruccion SQL
            strSQL = "select * from " + vsNombreTabla
            'Vaciar DataSet
            vDataSet.Clear()

            'Declarar DataAdapter para cargar combo
            Dim daTipoEquipo As New SqlDataAdapter(strSQL, SQLConn)

            daTipoEquipo.Fill(vDataSet, vsNombreTabla)

            daTipoEquipo = Nothing
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            'DataSet Nulo.
            vDataSet = Nothing
        Finally
            SQLConn.Close()
        End Try
        'Devolver DataSet
        Return vDataSet

    End Function
    Public Function ComenzarTransaccion() As SqlTransaction
        Dim dbTransaccion As SqlTransaction

        Try
            'Abrir Conexion ala BD
            SQLConn.Open()

            dbTransaccion = SQLConn.BeginTransaction
            Return dbTransaccion

        Catch ex As Exception

            Throw New Exception(ex.Message)
            Return Nothing

        End Try

    End Function

    Public Sub TerminarTransaccion(ByRef dbTransaccion As SqlTransaction)
        Try
            'Confirmar Transaccion
            dbTransaccion.Commit()

        Catch ex As Exception
            Throw New ApplicationException(ex.Message)
        Finally
            'Cerrar Conexion ala BD
            SQLConn.Close()
        End Try

    End Sub

    Public Sub DeshacerTransaccion(ByRef dbTransaccion As SqlTransaction)
        Try
            'Rollback Transaction
            dbTransaccion.Rollback()

        Catch ex As Exception
            Throw New ApplicationException(ex.Message)
        Finally
            'Cerrar Conexion ala BD
            SQLConn.Close()
        End Try

    End Sub


    Sub EjecutarComandoSQLTransaccion(ByVal strSQL As String, ByRef transaccion As SqlTransaction, ByVal ParamArray vParametros() As Object)
        Try
            'Crear Comando SQL
            SQLCmd = New SqlCommand(strSQL, SQLConn, transaccion) 'Crear comando, a partir del parametro recibido

            'Asignar Parametros al objeto Comando. Recorrer 
            Dim i As Integer = 0
            For Each vParametro As Object In vParametros
                i += 1
                Dim SQLParam As New SqlClient.SqlParameter()
                SQLCmd.Parameters.Add(New SqlClient.SqlParameter("@" & i.ToString(), vParametro))
            Next

            'Ejecutar Comando SQL
            SQLCmd.ExecuteNonQuery()

        Catch ex As Exception
            transaccion.Rollback()
            SQLConn.Close()
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub CargarImagen(ByVal codigo As String, ByVal ptbImage As PictureBox)
        Try
            ''Cargar(Fotografia)
            Dim daFoto As New SqlClient.SqlDataAdapter(codigo, SQLConn)
            Dim dsFoto As New DataSet()
            daFoto.Fill(dsFoto)
            Dim bits As Byte() = CType(dsFoto.Tables(0).Rows(0).Item(0), Byte())
            Dim memorybits As New MemoryStream(bits)
            Dim bitmap As New Bitmap(memorybits)
            ptbImage.Image = bitmap
            dsFoto.Dispose()
            daFoto.Dispose()
        Catch EX As Exception
            MessageBox.Show(EX.Message) ', "No se puede mostrar fotografia", MessageBoxButtons.OK, MessageBoxIcon.Information)
            ptbImage.Image = Nothing
            ptbImage.Refresh()
        End Try
    End Sub

End Module
